class Statement {
  // 查询语句
  select(tableName, options) {
    const { attrs = [], where = {}, order = {}, limit = {} } = options ?? {}
    const whereLength = Object.keys(where).length
    const orderLength = Object.keys(order).length
    const limitLength = Object.keys(limit).length
    let arrt_sta = '',
      start_sta = '',
      where_sta = '',
      limit_sta = '',
      order_sta = ''
    // 检查表名参数
    if (!tableName || typeof tableName !== 'string') {
      console.error('未设置表名称')
      return
    }
    // 是否设置了需要查询的字段，没有这默认查询所有
    if (attrs?.length) {
      for (const index in attrs) {
        if (typeof attrs[index] === 'object') {
          const fun = attrs[index].fun?.toUpperCase()
          const as = `${attrs[index].as ? ' ' + attrs[index].as : ''}`
          arrt_sta += `${index > 0 ? ', ' : ' '}${fun}(${attrs[index].attr})${as}`
        } else {
          arrt_sta += `${index > 0 ? ', ' : ' '}${attrs[index]}`
        }
      }
      start_sta = `SELECT${arrt_sta} FROM ${tableName}`
    } else {
      start_sta = `SELECT * FROM ${tableName}`
    }
    // 是否有where条件
    if (whereLength) {
      where_sta = createWhereStatmment(where)
    }
    // 是否排序
    if (orderLength) {
      for (let i in order) {
        if (i == 0) {
          order_sta += ` ORDER BY ${order[i][0]} ${order[i][1].toUpperCase()}`
        } else {
          order_sta += `, ${order[i][0]} ${order[i][1].toUpperCase()}`
        }
      }
    }
    // 是否有设置偏移
    if (limitLength) {
      const size = limit['pageSize'] ?? 10
      const offset = ((limit['pageNum'] ?? 1) - 1) * size
      limit_sta = ` LIMIT ${offset}, ${size}`
    }

    return start_sta + where_sta + order_sta + limit_sta + ';'
  }

  // 更新语句
  update(tableName, options) {
    const { attrs = {}, where = {} } = options
    const attrLength = Object.keys(attrs).length
    const whereLength = Object.keys(where).length

    let start_sta = '',
      attr_sta = '',
      where_sta = ''

    // 检查表名参数
    if (!tableName || typeof tableName !== 'string') {
      console.error('未设置表名称')
      return
    }

    if (attrLength) {
      start_sta = `UPDATE ${tableName} SET`

      let index = 1
      for (const key in attrs) {
        if (index == 1) {
          attr_sta += ` ${key} = ${valueType(attrs[key])}`
        } else {
          attr_sta += `, ${key} = ${valueType(attrs[key])}`
        }
        index++
      }
    }

    // 是否有where条件
    if (whereLength) {
      where_sta = createWhereStatmment(where)
    }

    return start_sta + attr_sta + where_sta + ';'
  }

  // 新增语句
  insert(tableName, payload) {
    const payloadLength = Object.keys(payload).length
    let start_sta = '',
      key_sta = '',
      value_sta = ''

    // 检查表名参数
    if (!tableName || typeof tableName !== 'string') {
      console.error('未设置表名称')
      return
    }

    if (payloadLength) {
      start_sta = `INSERT INTO ${tableName}`

      let index = 1
      for (const key in payload) {
        if (index == 1) {
          key_sta += `${key}`
          value_sta += `${valueType(payload[key])}`
        } else if (index <= payloadLength) {
          key_sta += `, ${key}`
          value_sta += `, ${valueType(payload[key])}`
        }
        index++
      }
    }

    return `${start_sta} (${key_sta}) VALUES (${value_sta});`
  }

  // 删除语句
  delete(tableName, where) {
    // 检查表名参数
    if (!tableName || typeof tableName !== 'string') {
      console.error('未设置表名称')
      return
    }

    const whereLength = Object.keys(where).length
    const start_sta = `DELETE FROM ${tableName}`
    let where_sta = ''

    // 是否有where条件
    if (whereLength) {
      where_sta = createWhereStatmment(where)
    }

    return start_sta + where_sta + ';'
  }
}

function createWhereStatmment(where) {
  const whereLength = Object.keys(where).length
  let where_sta = ''

  if (whereLength) {
    let index = 1
    where_sta += ' WHERE'
    for (const key in where) {
      const type = where[key].type ? where[key].type?.toUpperCase() : 'AND'
      const way = where[key].way ? where[key].way : '='

      if (typeof where[key] != 'object') {
        where_sta += `${index > 1 ? ' AND ' : ' '}${key} = '${where[key]}'`
        index++
      } else if (way === '=' && where[key].value) {
        where_sta += `${index > 1 ? ` ${type} ` : ' '}${key} = '${where[key].value}'`
        index++
      } else if (way === 'like' && where[key].value) {
        where_sta += `${index > 1 ? ` ${type} ` : ' '}${key} LIKE '%${where[key].value}%'`
        index++
      } else if (way === 'startsWith' && where[key].value) {
        where_sta += `${index > 1 ? ` ${type} ` : ' '}${key} LIKE '%${where[key].value}'`
        index++
      } else if (way === 'endsWith' && where[key].value) {
        where_sta += `${index > 1 ? ` ${type} ` : ' '}${key} LIKE '${where[key].value}%'`
        index++
      }
    }
  }
  return where_sta
}

// 判断值类型是否需要添加引号
function valueType(value) {
  if (typeof value === 'string') {
    return `'${value}'`
  } else {
    return value
  }
}

module.exports = new Statement()
